const db = require("../db/index");
const bcrypt = require("bcryptjs");
const jwt = require("jsonwebtoken");
const config = require("../config");
const moment = require("moment");

// 通用登录
exports.login = (req, res) => {
  let sql = `select * from user where username = ?`;
  db.query(sql, req.body.username, function (err, results) {
    // 执行 SQL 语句失败
    if (err) return res.cc(err);
    // 执行 SQL 语句成功，但是查询到数据条数不等于 1
    if (results.length < 1) return res.cc("登录失败，不存在的用户！");
    // 判断用户输入的登录密码是否和数据库中的密码一致
    const compareResult = bcrypt.compareSync(
      req.body.password,
      results[0].password
    );
    if (!compareResult) {
      return res.cc("密码错误！");
    }

    //登录成功
    const user = {
      ...results[0],
    };
    // 剔除密码，格式化日期，转为token
    delete user.password;
    delete user.user_pic; //todo 以后可以带
    user.birthday = moment(user.birthday).format("YYYYMMDD");
    
    // 生成 Token 字符串
    const tokenStr = jwt.sign(user, config.jwtSecretKey, {
      expiresIn: "10h", // token 有效期为 10 个小时
    });
    res.rt({
      message: "登录成功！",
      token: "Bearer " + tokenStr,
    });
  });
};
// 获取分类
exports.getClass = (req, res) => {
  let sql;
  if (req.query.type == 0) {
    sql = `select * from classification WHERE  is_delete = 0`;
    db.query(sql, function (err, results) {
      if (err) return res.cc(err);
      if (results.length < 1) return res.cc("失败！");
      res.rt({
        message: "获取成功！",
        results,
      });
    });
  } else if (req.query.type == 1) {
    const pagesize = parseInt(req.query.pagesize) || 1;
    const pagenum = (parseInt(req.query.pagenum) - 1) * pagesize || 0;
    sql1 = `select * from classification LIMIT ? , ? `;
    sql2 = `select count(*) as total from classification`;
    db.query(sql1, [pagenum, pagesize], function (err, results) {
      if (err) return res.cc(err);
      if (results.length < 1) return res.cc("失败！");
      db.query(sql2, function (err, data) {
        if (err) return res.cc(err);
        if (results.length < 1) return res.cc("失败！");
        res.rt({
          message: "获取成功！",
          total: data[0].total,
          results,
        });
      });
    });
  } else if (req.query.type == 2) {
    sql = `select * from classification WHERE  is_delete = 0 and class_id != 0`;
    db.query(sql, function (err, results) {
      if (err) return res.cc(err);
      if (results.length < 1) return res.cc("失败！");
      res.rt({
        message: "获取成功！",
        results,
      });
    });
  } else {
    res.cc("类型错误");
  }
};
// 获取文章列表
exports.getArticle = (req, res) => {
  if (req.query.type == 1) {
    // 管理员模式 分页 搜索
    const pagesize = parseInt(req.query.pagesize) || 1;
    const pagenum = (parseInt(req.query.pagenum) - 1) * pagesize || 0;
    const field = req.query.field || "";
    const sql = `select article.article_id,article.classid,article.title,article.intro,article.pageview,article.date,article.is_delete,classification.classname
  from article LEFT OUTER JOIN classification
  ON article.classid = classification.class_id
  where article_id !=0 and  (article.title like '%${field}%' or article.intro like '%${field}%')
  LIMIT ? , ? `;
    db.query(sql, [pagenum, pagesize], function (err, results) {
      if (err) return res.cc(err);
      if (results.length < 1) return res.cc("失败！");
      db.query(
        `select count(*) as total from article where article_id !=0 and (article.title like '%${field}%' or article.intro like '%${field}%')`,
        function (err, data) {
          if (err) return res.cc(err);
          if (data.length < 1) return res.cc("失败！");
          res.rt({
            total: data[0].total,
            message: "获取成功！",
            results,
          });
        }
      );
    });
  } else if (req.query.type == 0) {
    // 普通用户 分页
    const pagesize = parseInt(req.query.pagesize) || 1;
    const pagenum = (parseInt(req.query.pagenum) - 1) * pagesize || 0;
    const field = req.query.field || "";
    const sql = `select article.article_id,article.classid,article.title,article.intro,article.pageview,article.date,article.is_delete,classification.classname,COUNT(comment.comment_id) as commonts
    from (article LEFT OUTER JOIN classification ON article.classid = classification.class_id) left JOIN comment on  article.article_id = comment.articleid
  GROUP BY article_id
    HAVING article.article_id !=0  and article.is_delete = 0 AND classid != 0 and ((article.title like '%${field}%' or article.intro like '%${field}%'))
    ORDER BY RAND()
    LIMIT ? , ? `;
    db.query(sql, [pagenum, pagesize], function (err, results) {
      if (err) return res.cc(err);
      if (results.length < 1) return res.cc("失败！");
      res.rt({
        message: "获取成功！",
        results,
      });
    });
  } else if (req.query.type == 2) {
    // 普通用户 不分页
    const field = req.query.field || "";
    const sql = `select article.article_id,article.classid,article.title,article.intro,article.pageview,article.date,article.is_delete,classification.classname,COUNT(comment.comment_id) as commonts
    from (article LEFT OUTER JOIN classification ON article.classid = classification.class_id) left JOIN comment on  article.article_id = comment.articleid
  GROUP BY article_id
    HAVING article.article_id !=0  and article.is_delete = 0 AND classid != 0 and (article.title like '%${field}%' or article.intro like '%${field}%')
    ORDER BY RAND()
    `;
    
    db.query(sql, function (err, results) {
      if (err) return res.cc(err);
      if (results.length < 1) return res.cc("失败！");
      res.rt({
        message: "获取成功！",
        results,
      });
    });
  } else if (req.query.type == 3) {
    // 普通用户 不分页
    // 根据分类id
    const field = req.query.field || "";
    const sql = `select article.article_id,article.classid,article.title,article.intro,article.pageview,article.date,article.is_delete,classification.classname,COUNT(comment.comment_id) as commonts
    from (article LEFT OUTER JOIN classification ON article.classid = classification.class_id) left JOIN comment on  article.article_id = comment.articleid
  GROUP BY article_id
    HAVING article.article_id !=0  and article.is_delete = 0 AND classid != 0 and article.classid=? and (article.title like '%${field}%' or article.intro like '%${field}%')`;
    db.query(sql, req.query.class_id, function (err, results) {
      if (err) return res.cc(err);
      if (results.length < 1) return res.cc("失败！");
      res.rt({
        message: "获取成功！",
        results,
      });
    });
  } else {
    res.cc("类型错误");
  }
};
// 根据id获取文章信息
exports.getArticleInfo = (req, res) => {
  if (req.query.article_id == 0) return res.cc("不能查询0的id");
  const sql = `select article.article_id,article.title,article.intro,article.pageview,article.date,article.content,classification.class_id as classid
  from article JOIN classification ON article.classid=classification.class_id
  where article.article_id = ? and article.is_delete = 0`;
  db.query(sql, req.query.article_id, function (err, results) {
    if (err) return res.cc(err);
    if (results.length < 1) return res.cc("失败！");
    // 返回
    res.rt({
      message: "获取成功！",
      results: results[0],
    });
  });
};
// 获取页面数据
exports.getPageInfo = (req, res) => {
  const sql = `select * from pagedata where page_id = 1`;
  db.query(sql, function (err, results) {
    if (err) return res.cc(err);
    if (results.length < 1) return res.cc("失败！");
    res.rt({
      message: "获取成功！",
      results,
    });
  });
};
// 获取导航
exports.getNav = (req, res) => {
  if (req.query.type == 1) {
    const pagesize = parseInt(req.query.pagesize) || 1;
    const pagenum = (parseInt(req.query.pagenum) - 1) * pagesize || 0;
    const sql = `select * from navigation LIMIT ? , ? `;
    db.query(sql, [pagenum, pagesize], function (err, results) {
      if (err) return res.cc(err);
      if (results.length < 1) return res.cc("失败！");
      db.query(
        "select count(*) as total from navigation",
        function (err, data) {
          res.rt({
            total: data[0].total,
            message: "获取成功！",
            results,
          });
        }
      );
    });
  } else if (req.query.type == 0) {
    const sql = `select * from navigation where is_delete = 0`;
    db.query(sql, function (err, results) {
      if (err) return res.cc(err);
      if (results.length < 1) return res.cc("失败！");
      res.rt({
        message: "获取成功！",
        results,
      });
    });
  } else {
    res.cc("类型错误");
  }
};
//获取系统配置
exports.getSystem = (req, res) => {
  const sql = `select qq,wx,copyright,record
  from pagedata
  where page_id = 1`;
  db.query(sql, function (err, results) {
    if (err) return res.cc(err);
    if (results.length < 1) return res.cc("失败！");
    res.rt({
      message: "获取成功！",
      results: results[0],
    });
  });
};
// 根据id获取用户信息
exports.getUserInfo = (req, res) => {
  const sql = `select * from user where user_id=? and is_delete = 0`;
  db.query(sql, req.query.user_id, (err, results) => {
    if (err) return res.cc(err);
    if (results.length < 1) return res.cc("获取用户信息失败！");
    results[0].password = "";
    res.rt({
      message: "获取用户基本信息成功！",
      data: results[0],
    });
  });
};
// 注册
exports.regUser = (req, res) => {
  const userinfo = req.body;
  if (!userinfo.username || !userinfo.password) {
    return res.cc("用户名或密码不能为空！");
  }
  const sql = `select * from user where username=?`;
  db.query(sql, [userinfo.username], function (err, results) {
    if (err) {
      return res.cc(err.message);
    }
    if (results.length > 0) {
      return res.cc("用户名被占用，请更换其他用户名！");
    }
    userinfo.password = bcrypt.hashSync(userinfo.password, 10);
    const sql2 = "insert into user set ?";
    db.query(
      sql2,
      {
        username: userinfo.username,
        password: userinfo.password,
        cellphone: userinfo.cellphone,
        nickname: userinfo.nickname,
      },
      function (err, results) {
        // 执行 SQL 语句失败
        if (err)
          return res.cc({
            message: err.message,
          });
        if (results.affectedRows < 1) {
          return res.cc({
            message: "注册用户失败，请稍后再试！",
          });
        }
        res.rt("注册成功！");
      }
    );
    //
  });
};
// 获取我的生活文章
exports.getMyLife = (req, res) => {
  let sql;
  sql = `select article.article_id,article.title,article.intro,article.pageview,article.date,article.content,classification.class_id as classid
  from article JOIN classification ON article.classid=classification.class_id
  where article.classid = 0 and article.is_delete = 0 and article.article_id!=0`;
  db.query(sql, function (err, results) {
    if (err) return res.cc(err);
    if (results.length < 1) return res.cc("失败！");
    res.rt({
      message: "获取成功！",
      results,
    });
  });
};
// 递归树形结构
/**
 * @param {*} results 数据源
 * @param {*} parent_id  顶级id
 */
function get_tree(results, parent_id) {
  const initTree = (parent_id) => {
    // jsonArray 变量数据
    // 第一次以后：根据id去查询parent_id相同的（相同为子数据）
    // 第一次：查找所有parent_id为-1的数据组成第一级
    const child = results.filter((item) => item.lastid == parent_id);
    // 第一次：循环parent_id为-1数组
    return child.map((item) => ({
      ...item,
      // 当前存在id（id与parent_id应该是必须有的）调用initTree() 查找所有parent_id为本id的数据
      // childs字段写入
      childs: initTree(item.comment_id),
    }));
  };
  // 首先调用initTree方法查找所有parent_id为-1的（-1认为是第一级）
  const tree = initTree(parent_id);
  return tree;
}
// 根据文章id获取评论
exports.getCommontById = (req, res) => {
  const sql = `SELECT
	com.*,
	us.nickname,
	us.username,
	us.user_pic,
	u2.nickname AS tagname 
FROM
	( comment AS com JOIN user AS us ON com.userid = us.user_id )
	LEFT JOIN user AS u2 ON com.targetid = u2.user_id 
WHERE
	com.articleid = ? 
	AND us.is_delete = 0 
	AND com.is_delete = 0`;
  db.query(sql, req.query.article_id, function (err, results) {
    if (err) return res.cc(err);
    if (results.length < 1) return res.cc("失败！");

    res.rt({
      message: "获取成功！",
      data: get_tree(results, -1),
      total: results.length,
    });
  });
};

// 浏览量+1
exports.addPageview = (req, res) => {
  const sql = `update article set pageview = pageview+1 where article_id = ?`;
  db.query(sql, req.body.article_id, function (err, results) {
    if (err) return res.cc(err);
    if (results.length < 1) return res.cc("失败！");
    res.rt({
      message: "成功！",
    });
  });
};
